A Treasury Bill Price and Yield Calculator For Bills with More than 182 days to Maturity
By Fred Shipley, Ph.D.
Computerized Investing, March/April 1989
By Fred Shipley
The January/February issue of Computerized Investing contained a worksheet (TBILLS1.XLS) for determining the yield on Treasury bills with six months or less until maturity. The formulas here will do the same calculations for bills with maturities of more than six months. To generate the information needed, check a current Wall Street Journal, Barron's or other financial publication. Quotes are usually given as discount yields. The discount yield is the uncompounded yield, based on the discount price and a 360 day year. The coupon-equivalent yield is an uncompounded semiannual yield that can be compared with quoted yields to maturity for coupon bonds, such as corporate bonds.
Enter the discount yield information into cell D4, the maturity into cell D6, the current date into cell D7, and the spreadsheet will determine the coupon-equivalent yield. In entering the current date, we use the @NOW function that Release 2 of 1-2-3 provides.[Ed. Note: also in Excel] Since this function also returns the current time as a decimal part of the date, we use the integer rounding function (@INT) to remove it. Other spreadsheets may not contain this information. Failure to remove the time part of the date will result in some error in the price and yield calculations. If your spreadsheet does not support the @NOW function, you can use the @DATE function, which would appear as @DATE(year,month,day).
While this information is useful, an investor may also want to know the value of the bills for portfolio valuation purposes or for a possible sale. We use the bid yield, since that gives a price that has been offered to purchase bills (and the bid price will be lower than the asked price). Note that it is incorrect in labeling cell A4 as the asked yield--it is the bid yield.
For individuals wanting to program these formulas in another spreadsheet, or another language such as BASIC, the formulas are presented below.
F is the face value of the bill. ($10,000 is the minimum denomination of T bills; quotes in the media are usually for round lots of $1,000,000. Frequently quoted as a percentage so the face value would be 100.)
D is the dollar amount of the discount. Determined from the discount yield.
d is the discount yield. This figure is what is reported in the financial media. (We use the bid discount yield as reported in the Wall Street Journal in our example. )
t is the time to maturity, in number of days.
Yc is the coupon-equivalent yield for bills with more than 182 days until maturity.
You can combine the coupon-equivalent formula presented above with equation in the January/February issue to create a single worksheet which will handle bills of any maturity by using an @IF statement. The form of this statement is:
@IF(condition, true, false)
This means that 1-2-3 will examine the condition that appears within the parentheses to see if it is true or false. (Most spreadsheet programs and programming languages support a similar function.) If the statement is true, 1-2-3 will execute whatever appears next; if false, 1-2-3 will execute the last statement within the parentheses. These conditions or statements within the parentheses can be numbers, formulas, cell references or anything else that Lotus recognizes. The result is displayed in the cell in which the formula appears.
For example, if A1 contained the number 1, A2 contained the number 2, and cell A3 contained @IF(A1>A2,A1,A2); then A3 would show 2. Since A1 is 1 and A2 is 2, the statement A1>A2 is false. Thus A3 displays the false value, which is the value of A2--that is, 2.
To use the @IF function here, we must determine whether the difference between the maturity date and the current date is more than 182 days--six months. If so, we want to evaluate the equation for the yield, based on formula 3 above. If the difference is less than or equal to 182 days, we want to use formula (4), which appeared in the January/February issue.
Yc = 365d/(360 - dt) (4)
The complete form of the @IF statement should then be inserted into cell D12; the formula follows.
D12: @IF(($D$6-$D$7>182),(-2*($D$6-$D$7)/365+
(2*((($D$6-$D$7)/365)^2-(2*(($D$6-$D$7)/365)-1)*
(1-1/($D$10/$D$3)))^0.5))/(2*($D$6-$D$7)/365-1),
(365*$D$4)/(360-$D$4*($D$6-$D$7)))
For simplicity you may want to create a separate spreadsheet for each case--one for the six month or shorter maturities--and one for those longer than six months.
